Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# TRANSLATE ... USING

Syntax

Description of the illustration translate_using.gif

Purpose

`TRANSLATE` ... `USING` converts `char` into the character set specified for conversions between the database character set and the national character set.

Note: The `TRANSLATE` ... `USING` function is supported primarily for ANSI compatibility. Oracle recommends that you use the `TO_CHAR` and `TO_NCHAR` functions, as appropriate, for converting data to the database or national character set. `TO_CHAR` and `TO_NCHAR` can take as arguments a greater variety of datatypes than `TRANSLATE` ... `USING`, which accepts only character data.

The `char` argument is the expression to be converted.

- Specifying the `USING` `CHAR_CS` argument converts `char` into the database character set. The output datatype is `VARCHAR2`.

- Specifying the `USING` `NCHAR_CS` argument converts `char` into the national character set. The output datatype is `NVARCHAR2`.

This function is similar to the Oracle `CONVERT` function, but must be used instead of `CONVERT` if either the input or the output datatype is being used as `NCHAR` or `NVARCHAR2`. If the input contains UCS2 code points or backslash characters (\), then use the `UNISTR` function.

See Also: CONVERT and UNISTR

Examples

The following statements use data from the sample table `oe.product_descriptions` to show the use of the `TRANSLATE` ... `USING` function:

    CREATE TABLE translate_tab (char_col  VARCHAR2(100),
                                nchar_col NVARCHAR2(50));
    INSERT INTO translate_tab
       SELECT NULL, translated_name
          FROM product_descriptions
          WHERE product_id = 3501;

    SELECT * FROM translate_tab;

    CHAR_COL                  NCHAR_COL
    ------------------------- -------------------------
    . . .
                              C per a SPNIX4.0 - Sys
                              C pro SPNIX4.0 - Sys
                              C for SPNIX4.0 - Sys
                              C til SPNIX4.0 - Sys
    . . .

    UPDATE translate_tab
       SET char_col = TRANSLATE (nchar_col USING CHAR_CS);

    SELECT * FROM translate_tab;

    CHAR_COL                  NCHAR_COL
    ------------------------- -------------------------
    . . .
    C per a SPNIX4.0 - Sys    C per a SPNIX4.0 - Sys
    C pro SPNIX4.0 - Sys      C pro SPNIX4.0 - Sys
    C for SPNIX4.0 - Sys      C for SPNIX4.0 - Sys
    C til SPNIX4.0 - Sys      C til SPNIX4.0 - Sys
    . . .